<?php

class Lot extends BaseActiveRecord
{
    public static $aCurrencySymbol = array(
        'EURO' => '&euro;',
        'USD' => '&#36;',
        'GBP' => '&pound;',
        'RUR' => 'RUR&nbsp;',
        'BYR' => 'BYR&nbsp;',
    );


    public static function getCurrencies()
    {
        return array(
            'EURO',
            'USD',
            'GBP',
            'RUR',
            'BYR',
        );
    }

    public static function getPlacementTimes()
    {
        return array(
            'one_hour' => '+1 hour',
            'two_hours' => '+2 hour',
            'five_hours' => '+5 hour',
            'twelve_hours' => '+12 hour',
            'one_day' => '+1 day',
            'two_days' => '+2 day',
            'three_days' => '+3 day',
            'four_days' => '+4 day',
            'five_days' => '+5 day',
            'seven_days' => '+7 day',
        );
    }

    public static function getPlacementTimesNames()
    {
        return array(
            'one_hour' => 'Один час',
            'two_hours' => 'Два часа',
            'five_hours' => 'Пять часов',
            'twelve_hours' => 'Двенадцать часов',
            'one_day' => 'Один день',
            'two_days' => 'Два дня',
            'three_days' => 'Три дня',
            'four_days' => 'Четыре дня',
            'five_days' => 'Пять дней',
            'seven_days' => 'Семь дней',
        );
    }

    public static function getCurrencySymbol($sCurrencyCode)
    {
        return isset(self::$aCurrencySymbol[$sCurrencyCode])?self::$aCurrencySymbol[$sCurrencyCode]:'';
    }


    public function tableName()
    {
        return '{{lots}}';
    }

    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function getAvailableLots($sLotType = false, $iCount = 100500)
    {
/*
        $sSql = "   SELECT l.*, COUNT(b.bid_id) AS bids_count
                    FROM ".$this->tableName()." AS l
                         LEFT JOIN ".Bid::model()->tableName()." AS b ON (b.lot_id = l.lot_id)
                    WHERE (UNIX_TIMESTAMP(l.date_expired) > UNIX_TIMESTAMP())
                          ".($sLotType?" AND (l.type = '".$sLotType."') ":"")."
                    GROUP BY l.lot_id
                    ORDER BY l.date_created DESC
                    LIMIT ".intval($iCount);
*/
        $sSql = "   SELECT l.*, COUNT(b.bid_id) AS bids_count
                    FROM ".$this->tableName()." AS l
                         LEFT JOIN ".Bid::model()->tableName()." AS b ON (b.lot_id = l.lot_id)
                    WHERE (l.type = '".$sLotType."')
                    GROUP BY l.lot_id
                    ORDER BY l.date_created DESC
                    LIMIT ".intval($iCount);

        $aAvailableLots = Yii::app()->db->createCommand($sSql)->queryAll();

        return $aAvailableLots;
    }

    public function getMyLots($iUserId)
    {
        $aMyLot = array();
        
        $sSql = "   SELECT l.*, l.status AS lot_status, b.bid_id, b.rate, b.message, b.date_created AS bid_date_created, b.is_like, p.allow_info
                    FROM ".$this->tableName()." AS l
                    LEFT JOIN ".Bid::model()->tableName()." AS b ON (b.lot_id = l.lot_id)
                    LEFT JOIN ".User::model()->tableName()." AS u ON (u.user_id = b.user_id)
                    LEFT JOIN ".Profile::model()->tableName()." AS p ON (p.profile_id = u.profile_id)
                    WHERE (l.user_id = ".intval($iUserId).")
                    GROUP BY l.lot_id,b.bid_id
                    ORDER BY l.date_created DESC, b.date_created DESC;";

        $aMyLotRows = Yii::app()->db->createCommand($sSql)->queryAll();

        if($aMyLotRows)
        {
            foreach ($aMyLotRows as $aRow)
            {
                if(!isset($aMyLot[$aRow['lot_id']]))
                {
                    $aMyLot[$aRow['lot_id']] = array();
                    $aMyLot[$aRow['lot_id']]['lot_id'] = $aRow['lot_id'];
                    $aMyLot[$aRow['lot_id']]['lot_number'] = $aRow['lot_number'];
                    $aMyLot[$aRow['lot_id']]['tariff_plan_id'] = $aRow['tariff_plan_id'];
                    $aMyLot[$aRow['lot_id']]['type'] = $aRow['type'];
                    $aMyLot[$aRow['lot_id']]['currency'] = $aRow['currency'];
                    $aMyLot[$aRow['lot_id']]['amount'] = $aRow['amount'];
                    $aMyLot[$aRow['lot_id']]['desired_rate'] = $aRow['desired_rate'];
                    $aMyLot[$aRow['lot_id']]['desired_currency'] = $aRow['desired_currency'];
                    $aMyLot[$aRow['lot_id']]['bank'] = $aRow['bank'];
                    $aMyLot[$aRow['lot_id']]['is_transaction_through_a_bank_reference'] = $aRow['is_transaction_through_a_bank_reference'];
                    $aMyLot[$aRow['lot_id']]['is_urgent'] = $aRow['is_urgent'];
                    $aMyLot[$aRow['lot_id']]['date_created'] = $aRow['date_created'];
                    $aMyLot[$aRow['lot_id']]['date_expired'] = $aRow['date_expired'];
                    $aMyLot[$aRow['lot_id']]['status'] = $aRow['lot_status'];

                    $aMyLot[$aRow['lot_id']]['bids'] = array();
                }

                if (!empty($aRow['bid_id']))
                {
                    $aMyLot[$aRow['lot_id']]['bids'][$aRow['bid_id']] = array(
                        'bid_id' => $aRow['bid_id'],
                        'rate' => $aRow['rate'],
                        'message' => $aRow['message'],
                        'date_created' => $aRow['bid_date_created'],
                        'is_like' => $aRow['is_like'],
                        'allow_info' => $aRow['allow_info'],
                    );
                }
            }
        }
        return $aMyLot;
    }

    public function getAvgRate($sLotType)
    {
        $sSql = 'SELECT 
                    l.currency AS currency, AVG(b.rate) AS avg_rate
                 FROM '.$this->tableName().' AS l LEFT JOIN '.Bid::model()->tableName().' AS b ON (l.lot_id = b.lot_id) WHERE b.rate > 0 AND l.type = "'.$sLotType.'" GROUP BY l.currency;';
        $aAvgRate = Yii::app()->db->createCommand($sSql)->queryAll();
        return $aAvgRate;
    }

    public function getAvgRateYesterday($sLotType)
    {
        $aAvgRateYesterday = array();

        // @TODO change interval to 1 day

        $sSql = 'SELECT
                    l.currency AS currency, AVG(b.rate) AS avg_rate_yesterday
                 FROM '.$this->tableName().' AS l
                 LEFT JOIN '.Bid::model()->tableName().' AS b ON (l.lot_id = b.lot_id)
                 WHERE b.rate > 0 AND l.type = "'.$sLotType.'" AND (UNIX_TIMESTAMP(DATE(b.date_created)) < UNIX_TIMESTAMP(CURRENT_DATE() - interval 3 day))
                 GROUP BY l.currency;';
        $aRows = Yii::app()->db->createCommand($sSql)->queryAll();

        if($aRows)
        {
            foreach ($aRows as $aRow)
            {
                $aAvgRateYesterday[$aRow['currency']] = $aRow;
            }
        }

        return $aAvgRateYesterday;
    }

    public function getCurrencySum($sLotType)
    {
        $sSql = 'SELECT 
                    l.currency AS currency, SUM(l.amount) AS sum_currency
                 FROM '.$this->tableName().' AS l
                 WHERE l.amount > 0 AND l.type = "'.$sLotType.'"
                 GROUP BY l.currency;';
        $aAvgRate = Yii::app()->db->createCommand($sSql)->queryAll();
        return $aAvgRate;
    }

    public function getCurrencySumYesterday($sLotType)
    {
        $aCurrencySumYesterday = array();

        // @TODO change interval to 1 day

        $sSql = 'SELECT
                    l.currency AS currency, SUM(l.amount) AS sum_currency_yesterday
                 FROM '.$this->tableName().' AS l
                 WHERE l.amount > 0 AND l.type = "'.$sLotType.'" AND (UNIX_TIMESTAMP(DATE(l.date_created)) < UNIX_TIMESTAMP(CURRENT_DATE() - interval 3 day))
                 GROUP BY l.currency;';
        $aRows = Yii::app()->db->createCommand($sSql)->queryAll();

        if($aRows)
        {
            foreach ($aRows as $aRow)
            {
                $aCurrencySumYesterday[$aRow['currency']] = $aRow;
            }
        }

        return $aCurrencySumYesterday;
    }

    public function rules()
    {
        $aPlacementTimes = self::getPlacementTimes();

        return array
        (
            array('tariff_plan_id, type, placement_time, currency, amount, desired_rate, desired_currency', 'required', 'on' => 'insert'),
            array('amount, desired_rate, desired_currency', 'required', 'on' => 'update'),
            array('amount, desired_rate', 'CNumberValidator'),
            array('bank', 'safe'),
            array('bank', 'length', 'max'=>255),
            array('type', 'CRangeValidator', 'range'=>array('sale', 'purchase')),
            array('currency, desired_currency', 'CRangeValidator', 'range'=>self::getCurrencies()),
            array('placement_time', 'CRangeValidator', 'range'=>  array_keys($aPlacementTimes)),
        );
    }

    public function  checkboxes()
    {
        return array
        (
            'is_transaction_through_a_bank_reference',
            'is_urgent'
        );
    }

    public function attributeLabels()
    {
        return array
        (
            'lot_number' => 'ID Лота',
            'tariff_plan_id'  => 'ID Тарифного плана',
            'tariff_plan.name'  => 'Тарифный план',
            'type'  => 'Тип лота',
            'currency'  => 'Валюта',
            'amount'  => 'Сумма',
            'desired_rate'  => 'Желаемый курс',
            'desired_currency'  => 'Желаемая валюта',
            'bank'  => 'Ваш банк (код МФО)',
            'is_transaction_through_a_bank_reference'  => 'Возможна сделка через опорный банк',
            'is_urgent'  => 'Срочная продажа',
            'date_created'  => 'Дата начала',
            'date_expired'  => 'Дата окончания',
        );
    }
}